package com.firewolf.struts.fileupload.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

import com.mysql.jdbc.ResultSetMetaData;
/**
 * 数据库操作工具类
 * @author liuxing
 *
 */
public class DBUtil {
	
	private static String url = null;
	private static String user = null;
	private static String password = null;
	
	private static Connection conn = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;
	
	/**
	 * 
	 * @return
	 */
	private  DBUtil(){
	}
	
	/**
	 * 返回单例，这样只创建一个数据库对象，节省资源
	 * @return
	 */
	public static DBUtil getInstance(){
		return new DBUtil();
	}
	
	/**
	 * 静态语句块，加载配置文件
	 */
	static{
		try {
			InputStream inputstream = DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties prop = new Properties();
			prop.load(inputstream);
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
			Class.forName(prop.getProperty("driver"));
			conn = getConnection();
		} catch (Exception e) {
			System.out.println(url);
			e.printStackTrace();
		}
	}
	
	/**
	 * 得到与数据库的连接
	 * @return
	 */
	private static Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			System.out.println("没有连接上");
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 更新数据，用于对数据的增删改
	 * @param sql
	 * @param paras
	 * return 执行成功返回true
	 */
	public boolean doUpdate(String sql, Object[] paras) throws SQLException {
		boolean success = false;
		ps = conn.prepareStatement(sql);
		if (paras != null) {
			for (int i = 0; i < paras.length; i++) {
				ps.setObject(i + 1, paras[i]);
			}
		}
		int returncount = ps.executeUpdate();
		if (returncount != 1) {
			success = true;
		}
		return success;
	}
	
	
	/**
	 * 用于查询数据
	 * 
	 * @param sql
	 * @param paras
	 * @return 返回一个ArrayList
	 */
	public ArrayList<Object[]> doQuery(String sql, Object[] paras) throws SQLException {
		ArrayList<Object[]> aList = new ArrayList<Object[]>();
		ps = conn.prepareStatement(sql);
		if (paras != null) {
			for (int i = 0; i < paras.length; i++) {
				ps.setObject(i + 1, paras[i]);
			}
		}
		rs = ps.executeQuery();
		ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
		int column = rsmd.getColumnCount();
		while(rs.next()){
		    Object[] obj=new Object[column];
		    for(int i=1;i<=column;i++)
		    {
		    	obj[i-1]=rs.getObject(i);
		    }
		    aList.add(obj);    
		 }
		return aList;
	}
	
	/**
	 * 关闭数据库资源
	 */
	public void close() {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
